#include "dbhelper.h"
#include <QDebug>
dbhelper* dbhelper::instance = nullptr;

dbhelper::dbhelper()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("data.db");
    if(!db.open())
    {
        qDebug()<<"数据库打开失败"<<db.lastError().text();
        return;
    }
    initUserTable();
    initGroupTables();
}

dbhelper::~dbhelper()
{
    if(db.isOpen())
    {
        db.close();
    }
}

void dbhelper::initUserTable()
{
    QString sqlStr = "CREATE TABLE IF NOT EXISTS userinfo(name TEXT PRIMARY KEY,pswd TEXT NOT NULL,online INTEGER DEFAULT 0);";
    QSqlQuery query(db);
    bool ret = query.exec(sqlStr);
    if(!ret)
    {
        qDebug()<<"用户数据表初始化失败"<<query.lastError().text();
    }
}
void dbhelper::initGroupTables()
{
    // 讨论组表
    QString createGroupTable =
        "CREATE TABLE IF NOT EXISTS groups ("
        "id TEXT PRIMARY KEY,"
        "name TEXT NOT NULL,"
        "creator TEXT NOT NULL,"
        "create_time DATETIME DEFAULT CURRENT_TIMESTAMP,"
        "FOREIGN KEY(creator) REFERENCES userinfo(name));";

    // 讨论组成员表
    QString createMemberTable =
        "CREATE TABLE IF NOT EXISTS group_members ("
        "group_id TEXT NOT NULL,"
        "member_name TEXT NOT NULL,"
        "join_time DATETIME DEFAULT CURRENT_TIMESTAMP,"
        "PRIMARY KEY(group_id, member_name),"
        "FOREIGN KEY(group_id) REFERENCES groups(id),"
        "FOREIGN KEY(member_name) REFERENCES userinfo(name));";

    QSqlQuery query(db);
    if(!query.exec(createGroupTable)) {
        qDebug()<<"讨论组表初始化失败"<<query.lastError().text();
    }

    if(!query.exec(createMemberTable)) {
        qDebug()<<"讨论组成员表初始化失败"<<query.lastError().text();
    }
}
dbhelper *dbhelper::getInstance()
{
    if(instance == nullptr)
    {
        instance = new dbhelper;
    }
    return instance;
}

void dbhelper::removeInstance()
{
    if(instance!=nullptr)
    {
        delete instance;
        instance = nullptr;
    }
}

bool dbhelper::userRegist(QString name, QString pswd)
{
    QSqlQuery query(db);
    query.prepare("INSERT INTO userinfo(name,pswd) VALUES(:name,:pswd)");
    query.bindValue(":name",name);
    query.bindValue(":pswd",pswd);

    return query.exec();
}

bool dbhelper::userLogin(QString name, QString pswd)
{
    QSqlQuery query(db);
    query.prepare("SELECT * FROM userinfo WHERE name=:name and pswd=:pswd and online=0");
    query.bindValue(":name",name);
    query.bindValue(":pswd",pswd);
    bool ret = query.exec();
    if(!ret)
    {
        qDebug()<<"登录命令错误"<<query.lastError().text();
        return false;
    }
    ret = query.next();
    if(ret)
    {
        setOnlineStatus(name,true);
    }
    return ret;
}
void dbhelper::setOnlineStatus(QString name, bool status)
{
    QSqlQuery query(db);
    query.prepare("UPDATE userinfo SET online=:online WHERE name=:name");
    query.bindValue(":name",name);
    query.bindValue(":online",status);
    bool ret = query.exec();
    if(!ret)
    {
        qDebug()<<"设置登录状态命令错误"<<query.lastError().text();
    }
//    else
//    {
//        qDebug()<<"用户在线状态设置成功";
//    }
}
QString dbhelper::createGroup( const  QString &name,const  QString &creator)
{
    QString groupId = QUuid::createUuid().toString();

    QSqlQuery query(db);
    query.prepare("INSERT INTO groups(id, name, creator) VALUES(:id, :name, :creator)");
    query.bindValue(":id", groupId);
    query.bindValue(":name", name);
    query.bindValue(":creator", creator);
    qDebug()<<name<<groupId<<creator;

    if (query.exec() && addGroupMember(groupId, creator)) {
         //qDebug()<<"群id"<<groupId;
            return groupId;

        } else {
            qDebug() << "创建群组失败：" << query.lastError().text();
            return "";
        }
//    if(query.exec()) {
//        // 创建者自动加入群组
//        if(addGroupMember(groupId, creator)) {
//            return groupId;
//        }
//    }
//    qDebug()<<"创建讨论组失败:"<<query.lastError().text();
//    return "";
}

bool dbhelper::addGroupMember(const QString &groupId, const QString &memberName)
{
    QSqlQuery query(db);
    query.prepare("INSERT INTO group_members(group_id, member_name) VALUES(:group_id, :member_name)");
    query.bindValue(":group_id", groupId);
    query.bindValue(":member_name", memberName);

    bool ret=query.exec();
    qDebug()<<"添加"<<ret<<groupId<<memberName;
    return ret;
}

QJsonArray dbhelper::getGroupList(const QString &username)
{
    QJsonArray groups;

    QSqlQuery query(db);
    query.prepare(
        "SELECT g.id, g.name FROM groups g "
        "JOIN group_members gm ON g.id = gm.group_id "
        "WHERE gm.member_name = :username");
    query.bindValue(":username", username);

    if(query.exec()) {
        while(query.next()) {
            QJsonObject group;
            group["id"] = query.value("id").toString();
            group["name"] = query.value("name").toString();
            groups.append(group);
        }
    } else {
        qDebug()<<"获取讨论组列表失败:"<<query.lastError().text();
    }

    return groups;
}

QJsonArray dbhelper::getGroupMembers(const QString &groupId)
{
    QJsonArray members;

    QSqlQuery query(db);
    query.prepare(
        "SELECT member_name FROM group_members "
        "WHERE group_id = :group_id");
    query.bindValue(":group_id", groupId);

    if(query.exec()) {
        while(query.next()) {
            members.append(query.value("member_name").toString());
        }
    }

    return members;
}

bool dbhelper::isGroupMember(const QString &groupId, const QString &memberName)
{
    QSqlQuery query(db);
    query.prepare(
        "SELECT 1 FROM group_members "
        "WHERE group_id = :group_id AND member_name = :member_name");
    query.bindValue(":group_id", groupId);
    query.bindValue(":member_name", memberName);

    return query.exec() && query.next();
}

QString dbhelper::getGroupName(const QString &groupId)
{
    QSqlQuery query(db);
    query.prepare("SELECT name FROM groups WHERE id = :id");
    query.bindValue(":id", groupId);

    if(query.exec() && query.next()) {
        return query.value("name").toString();
    }

    return "";
}
